﻿CREATE PROCEDURE [dbo].[pr_GocExplanationByOt](@otName varchar(50))
AS
BEGIN
	IF object_id('tempdb..#ExplanationByOT') IS NOT NULL 
	BEGIN
		 DROP TABLE #ExplanationByOT
	END

	IF object_id('tempdb..#ExplanationResultByOT') IS NOT NULL 
	BEGIN
		 DROP TABLE #ExplanationResultByOT
	END

	select
	p.Goc, 
	p.AccountLeaf, 
	p.ScenarioId,
	r.Name,
	p.NatSignJanUSDAmount,
	p.NatSignFebUSDAmount,
	p.NatSignMarUSDAmount,
	p.NatSignAprUSDAmount,
	p.NatSignMayUSDAmount,
	p.NatSignJunUSDAmount,
	p.NatSignJulUSDAmount,
	p.NatSignAugUSDAmount,
	p.NatSignSepUSDAmount,
	p.NatSignOctUSDAmount,
	p.NatSignNovUSDAmount,
	p.NatSignDecUSDAmount
	into #ExplanationByOT
	from Pearl p 
	inner join goc g  on
	p.Goc = g.GocAttribute
	inner join [dbo].[Rule] r on
	g.RuleId = r.Id
	where p.AccountLeaf in (2829,2830) and
	r.Name = @otName and
	p.ScenarioId in (1, 2)
	order by p.Goc

	select 
	g.GocAttribute,
	r.Name,
	ISNULL((select top 1 e.NatSignJanUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS JanPlan,
	ISNULL((select top 1 e.NatSignJanUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS JanActual,
	ISNULL((select top 1 e.NatSignFebUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS FebPlan,
	ISNULL((select top 1 e.NatSignFebUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS FebActual,
	ISNULL((select top 1 e.NatSignMarUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS MarPlan,
	ISNULL((select top 1 e.NatSignMarUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS MarActual,
	ISNULL((select top 1 e.NatSignAprUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS AprPlan,
	ISNULL((select top 1 e.NatSignAprUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS AprActual,
	ISNULL((select top 1 e.NatSignMayUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS MayPlan,
	ISNULL((select top 1 e.NatSignMayUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS MayActual,
	ISNULL((select top 1 e.NatSignJunUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS JunPlan,
	ISNULL((select top 1 e.NatSignJunUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS JunActual,
	ISNULL((select top 1 e.NatSignJulUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS JulPlan,
	ISNULL((select top 1 e.NatSignJulUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS JulActual,
	ISNULL((select top 1 e.NatSignAugUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS AugPlan,
	ISNULL((select top 1 e.NatSignAugUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS AugActual,
	ISNULL((select top 1 e.NatSignSepUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS SepPlan,
	ISNULL((select top 1 e.NatSignSepUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS SepActual,
	ISNULL((select top 1 e.NatSignOctUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS OctPlan,
	ISNULL((select top 1 e.NatSignOctUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS OctActual,
	ISNULL((select top 1 e.NatSignNovUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS NovPlan,
	ISNULL((select top 1 e.NatSignNovUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS NovActual,
	ISNULL((select top 1 e.NatSignDecUSDAmount from #ExplanationByOT e where e.ScenarioId = 1 and e.Goc = g.GocAttribute), 0) AS DecPlan,
	ISNULL((select top 1 e.NatSignDecUSDAmount from #ExplanationByOT e where e.ScenarioId = 2 and e.Goc = g.GocAttribute), 0) AS DecActual
	into #ExplanationResultByOT
	from goc g 
	inner join [dbo].[Rule] r on
	g.RuleId = r.Id
	where r.Name = @otName

	select erbo.GocAttribute,
	erbo.Name,
	CAST(erbo.JanPlan AS INT) AS JanPlan,
	CAST(erbo.JanActual AS INT) AS JanActual,
	CAST((erbo.JanPlan - erbo.JanActual) AS INT) As JanVar,
	CAST(erbo.FebPlan AS INT) AS FebPlan,
	CAST(erbo.FebActual AS INT) AS FebActual,
	CAST((erbo.FebPlan - erbo.FebActual) AS INT) As FebVar,
	CAST(erbo.MarPlan AS INT) AS MarPlan,
	CAST(erbo.MarActual AS INT) AS MarActual,
	CAST((erbo.MarPlan - erbo.MarActual) AS INT) As MarVar,
	CAST(erbo.AprPlan AS INT) AS AprPlan,
	CAST(erbo.AprActual AS INT) AS AprActual,
	CAST((erbo.AprPlan - erbo.AprActual) AS INT) As AprVar,
	CAST(erbo.MayPlan AS INT) AS MayPlan,
	CAST(erbo.MayActual AS INT) AS MayActual,
	CAST((erbo.MayPlan - erbo.MayActual) AS INT) As MayVar,
	CAST(erbo.JunPlan AS INT) AS JunPlan,
	CAST(erbo.JunActual AS INT) AS JunActual,
	CAST((erbo.JunPlan - erbo.JunActual) AS INT) As JunVar,
	CAST(erbo.JulPlan AS INT) AS JulPlan,
	CAST(erbo.JulActual AS INT) AS JulActual,
	CAST((erbo.JulPlan - erbo.JulActual) AS INT) As JulVar,
	CAST(erbo.AugPlan AS INT) AS AugPlan,
	CAST(erbo.AugActual AS INT) AS AugActual,
	CAST((erbo.AugPlan - erbo.AugActual) AS INT) As AugVar,
	CAST(erbo.SepPlan AS INT) AS SepPlan,
	CAST(erbo.SepActual AS INT) AS SepActual,
	CAST((erbo.SepPlan - erbo.SepActual) AS INT) As SepVar,
	CAST(erbo.OctPlan AS INT) AS OctPlan,
	CAST(erbo.OctActual AS INT) AS OctActual,
	CAST((erbo.OctPlan - erbo.OctActual) AS INT) As OctVar,
	CAST(erbo.NovPlan AS INT) AS NovPlan,
	CAST(erbo.NovActual AS INT) AS NovActual,
	CAST((erbo.NovPlan - erbo.NovActual) AS INT) As NovVar,
	CAST(erbo.DecPlan AS INT) AS DecPlan,
	CAST(erbo.DecActual AS INT) AS DecActual,
	CAST((erbo.DecPlan - erbo.DecActual) AS INT) As DecVar,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/01' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as JanVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/01' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as JanOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/01' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as JanProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/02' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as FebVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/02' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as FebOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/02' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as FebProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/03' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as MarVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/03' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as MarOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/03' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as MarProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/04' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as AprVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/04' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as AprOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/04' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as AprProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/05' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as MayVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/05' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as MayOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/05' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as MayProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/06' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as JunVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/06' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as JunOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/06' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as JunProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/07' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as JulVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/07' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as JulOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/07' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as JulProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/08' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as AugVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/08' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as AugOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/08' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as AugProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/09' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as SepVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/09' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as SepOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/09' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as SepProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/10' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as OctVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/10' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as OctOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/10' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as OctProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/11' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as NovVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/11' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as NovOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/11' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as NovProjects,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/12' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId in (2, 3, 4, 5)) as DecVolumes,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/12' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 1) as DecOpenPositions,
	(select COUNT(1) from JustifyPosition jp inner join Position p on jp.PositionId = p.Id
	where CONVERT(VARCHAR(7), jp.Date, 111) = CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/12' and p.GocAttribute = erbo.GocAttribute and jp.JustifyStatusId = 6) as DecProjects
	from #ExplanationResultByOT erbo
END